package com.csu.db;

import com.csu.ds.ExpdItem;
import com.csu.ds.IncomeItem;
import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.*;
import java.util.ArrayList;

public class DBUtils {

    private static ComboPooledDataSource dataSource= new ComboPooledDataSource();

    public static Connection getConnection(){
//        try{
//            return dataSource.getConnection();
//        }catch (SQLException e){
//            e.printStackTrace();
//        }
//
//        return null;

        //1,加载驱动
        try{
            Class.forName("com.mysql,jdbc.Driver");
        }catch (ClassNotFoundException e){

        }

        //2,定义url
        String url = "jdbc:mysql://localhost:3306/ffms?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT&allowPublicKeyRetrieval=true";
        String user = "root";
        String password = "123456";

        //3,创建链接
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;

    }

    public static void close(ResultSet rs, Statement stmt, Connection conn){
        if(rs != null){
            try{
                rs.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }

    public static ArrayList<ExpdItem> getExpenditureData(){
        ArrayList<ExpdItem> expdList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null; //sql语句的预编译对象
        ResultSet rs = null;

        conn = DBUtils.getConnection();

        //创建PreparedStatement对象
        try{
            String sql = "SELECT * FROM outlay";
            ps = conn.prepareStatement(sql);
            //执行
            rs = ps.executeQuery();
            //解析结果集
            while(rs.next()){
                ExpdItem expdItem = new ExpdItem();
                expdItem.setSN(rs.getInt("编号"));
                expdItem.setType(rs.getString("支出类型"));//取第一个字段的值
                expdItem.setPerson(rs.getString("支出者"));
                expdItem.setDate(rs.getDate("支出日期"));
                expdItem.setAmount(rs.getDouble("支出金额"));

                expdList.add(expdItem);
            }
            return expdList;
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.close(rs, ps, conn);
        }

        return null;

    }

    public static ArrayList<IncomeItem> getIncomeData(){
        ArrayList<IncomeItem> incomeList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null; //sql语句的预编译对象
        ResultSet rs = null;

        conn = DBUtils.getConnection();

        //创建PreparedStatement对象
        try{
            String sql = "SELECT * FROM income";
            ps = conn.prepareStatement(sql);
            //执行
            rs = ps.executeQuery();
            //解析结果集
            while(rs.next()){
                IncomeItem incomeItem = new IncomeItem();
                incomeItem.setEarner(rs.getString("EARNER"));//取第一个字段的值
                incomeItem.setDate(rs.getDate("INCOMEDATE"));
                incomeItem.setAmount(rs.getDouble("AMOUNT"));

                incomeList.add(incomeItem);
            }
            return incomeList;
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.close(rs, ps, conn);
        }

        return null;
    }

}
